<?php
abstract  class lib_db_sql {
	
	/**
	 * 操作的表
	 *
	 * @var string
	 */
	protected $table;
	
	protected $key='ID';
	
	protected $db;
	
	public $order;
	
	public $sql;
	

	/**
	 * 启用分页
	 *
	 * @var bool
	 */
	public $page=false;
	
	public  function __construct(){
		$this->db=R::getClass('db');
		$config=R::getClass('config');
		$db_prefix=$config->db[$config->theme]['db_prefix'];
		$this->table=str_replace('#',$db_prefix,$this->table);
	}	

	/**
	 * type的不同
	 * 得到insert,update,多条件delete,sql语句
	 *
	 * @param array $array
	 * @param string $type
	 * @return sql  string
	 */
	protected  function exarraysql($array=array(),$type='insert'){
		$ary=array();
		$sql=$where='';
		foreach ($array as $k=>$v){
				$ary[]=$k."='".$v."'";
		}		
		switch ($type){
			case 'insert': #insert语句
			    $sql="INSERT INTO ".$this->table." SET ".implode(',',$ary);				
				break;
			case 'update':#update语句,where id=* ,数组无ID值，返回假
				if(is_array($ary) && sizeof($array)>0){
					$sql="UPDATE ".$this->table." SET ".implode(',',$ary);
					if(isset($array[$this->key])){
						$sql.="  WHERE {$this->key}=".(int)$array[$this->key];
					}
				}				
				break;
			case 'delete':#delte语句，根据$array去WHERE
				$sql="DELETE FROM ".$this->table." WHERE ";
				if(is_array($array) && sizeof($array)>0){
						foreach ($array as $key=>$val){
							$sql.=$key."='".$val."' AND ";
						}
						$sql=substr($sql,0,-4);
				}
				break;
			default:
				//$ary[]="create_data='".date('Y-m-d H:i:s')."'";
			    $sql="INSERT INTO ".$this->table." SET ".implode(',',$ary);			
				break;
		}
		$this->sql=$sql;
		return $sql;
	}
	
	/**
	 * 删除ID记录
	 *
	 * @param int $id
	 * @return string
	 */
	protected  function delete_id($id){  
		$sql="DELETE FROM $this->table WHERE {$this->key}=".(int)$id." LIMIT 1";
		$this->sql=$sql;
		return $sql;
	}
	
	/**
	 * 查询ID记录
	 *
	 * @param int $id
	 * @return string
	 */
	protected function select_id($id){
		$sql="SELECT * FROM $this->table WHERE {$this->key}=".(int)$id;
		$this->sql=$sql;
		return $sql;
	}
	
	/**
	 * 多条件的select
	 *
	 * @param array $where
	 * @return string
	 */
	protected function selectWhere($where=array()){
		$sql="SELECT * FROM $this->table ";
		if(!empty($where)){
			$sql.=" WHERE ";
			foreach ($where as $key=>$val){
				$sql.=$key."='".$val."' AND ";
			}
			$sql=substr($sql,0,-4);
		}
		if(!empty($this->order)){
		    $sql.=" ORDER BY  ".$this->order." ";
		}else{
		   $sql.=" ORDER BY {$this->key} DESC "; 
		}
		//$sql.=" ORDER BY create_data DESC ";
		if($this->page){
			$sql.=pageHTML::pageLimt($sql);
		}
		$this->sql=$sql;
		return $sql;
	}
	
	/**
	 * 查找指定的字段
	 *
	 * @param array $where
	 * @param array $col
	 * @return string
	 */
	protected   function selectColm($where=array(),$col=array(),$page=true){
		if(!empty($col)){
			if(is_array($col)){
				$colsql=implode(',',$col);
			}else{
				$colsql=$col;
			}
		}else{
			$colsql=' name ';
		}
		$sql="SELECT ".$colsql." FROM $this->table ";
		if(!empty($where)){
			$sql.=" WHERE ";
			foreach ($where as $key=>$val){
				$sql.=$key."='".$val."' AND ";
			}
			$sql=substr($sql,0,-4);
		}
		if(!empty($this->order)){
		    $sql.=" ORDER BY ".$this->order." ";
		}else{
		   $sql.=" ORDER BY {$this->key} DESC "; 
		}
		if($this->page && $page){
			$sql.=pageHTML::pageLimt();
		}		
		$this->sql=$sql;	
		return $sql;	
	}
	
	/**
	 * 得到记录数和
	 *
	 * @param array $where
	 * @return string
	 */
	protected function totalRow($where=array()){
		$sql="SELECT COUNT(1) AS total FROM $this->table ";
		if(!empty($where)){
			$sql.=" WHERE ";
			foreach ($where as $key=>$val){
				$sql.=$key."='".$val."' AND ";
			}			
			$sql=substr($sql,0,-4);
		}
		$this->sql=$sql;
		return $sql;
	}
}